package com.lagou.module01.dao;

import com.lagou.module01.entity.PageBean;
import com.lagou.module01.entity.Student;
import com.lagou.module01.util.DruidUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class StudentDaoImp implements StudentDao {

    /**
     * 自定义成员方法实现显示所有学生信息的功能
     * @param pageBean
     * @return
     */
    @Override
    public List<Student> studentShow(PageBean pageBean) {
        List<Student> studentList = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.使用StringBuilder类型的对象描述SQL语句的目的在于后续可以发生改变
            StringBuilder stringBuilder = new StringBuilder("select * from t_student");
            //3.当输入分页需求时进行分页查询，于是拼接SQL语句
            if (pageBean != null) {
                stringBuilder.append(" limit " + pageBean.getStart() + "," + pageBean.getRow());
            }
            //4.获取预处理对象并执行SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            //5.处理结果集 将结果集中的数据取出并放入到List集合中
            studentList = new ArrayList<>();
            while (resultSet.next()){
                Student student = new Student(resultSet.getInt("sid"),resultSet.getString("name"),
                        resultSet.getString("sex"),resultSet.getString("birthday"),
                        resultSet.getString("email"),resultSet.getString("remark"));
                studentList.add(student);
            }
            return studentList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 自定义方法实现获取数据表的总条数
     * @return
     */
    @Override
    public int selectSumPage() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.准备sql语句
            String sql = "select count(*) from t_student";
            //3.获取预处理对象并执行sql语句
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //4.处理结果集对象
            while (resultSet.next()){
                int sumPage = resultSet.getInt(1);
                return sumPage;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    /**
     * 自定义成员方法实现添加学生信息的功能
     * @param student
     * @return
     */
    @Override
    public int studentAdd(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.准备sql语句
            String sql = "insert into t_student values(null,?,?,?,?,?)";
            //3.获取预处理对象并执行SQL语句
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getSex());
            preparedStatement.setString(3, student.getBirthday());
            preparedStatement.setString(4,student.getEmail());
            preparedStatement.setString(5,student.getRemark());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4.释放资源
            DruidUtils.close(connection,preparedStatement);
        }
        return 0;
    }

    /**
     * 自定义成员方法实现通过学号删除学生信息的功能
     * @param id
     * @return
     */
    @Override
    public int studentRemove(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.准备sql语句
            String sql = "delete from t_student where sid = ?";
            //3.获取预处理对象并执行SQL语句
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4.释放资源
            DruidUtils.close(connection,preparedStatement);
        }
        return 0;
    }

    /**
     * 自定义成员方法实现学生信息的修改功能
     * @param student
     * @return
     */
    @Override
    public int studentUpdate(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.准备sql语句
            String sql = "update t_student set name = ?, sex = ?, birthday = ?, email = ?, remark = ? where sid = ?";
            //3.获取预处理对象并执行sql语句
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getSex());
            preparedStatement.setString(3,student.getBirthday());
            preparedStatement.setString(4,student.getEmail());
            preparedStatement.setString(5,student.getRemark());
            preparedStatement.setInt(6,student.getSid());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4.释放资源
            DruidUtils.close(connection,preparedStatement);
        }
        return 0;
    }

    /**
     * 自定义成员方法通过学号实现学生信息的查询功能
     * @param id
     * @return
     */
    @Override
    public Student getStudentById(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接
            connection = DruidUtils.getConnection();
            //2.准备sql语句
            String sql = "select * from t_student where sid = ?";
            //3.获得预处理对象并执行sql语句
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            //4.处理结果集并将其添加到Student对象中
            while (resultSet.next()) {
                Student student = new Student(id,resultSet.getString("name"),
                        resultSet.getString("sex"), resultSet.getString("birthday"),
                        resultSet.getString("email"),resultSet.getString("remark")
                        );
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.释放资源
            DruidUtils.close(connection,preparedStatement,resultSet);
        }
        return null;
    }


}
